﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Agenda.Entities;
using System.Data;
using System.Data.OleDb;


namespace Agenda.Repositories
{
    public class PersonRepository
    {
        private string connection = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" + DataConnection.DBConn + ";";

        public void Add(Person person)
        {
            using (OleDbConnection conn = new OleDbConnection(connection))
            {
                string statement =
                    string.Format("INSERT INTO Person ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
                    , DBObjects.Person.name.ToString()
                    , DBObjects.Person.surname.ToString()
                    , DBObjects.Person.cellphone1.ToString()
                    , DBObjects.Person.cellphone2.ToString()
                    , DBObjects.Person.telephone.ToString()
                    , DBObjects.Person.obra.ToString()
                    , DBObjects.Person.email.ToString()
                    , DBObjects.Person.extraInformation.ToString());
                    //, DBObjects.Person.userId.ToString());

                OleDbCommand command = new OleDbCommand(statement, conn);

                command.Parameters.Add(DBObjects.Person.name.ToString(), OleDbType.VarChar).Value = person.Name;
                command.Parameters.Add(DBObjects.Person.surname.ToString(), OleDbType.VarChar).Value = person.Surname;
                command.Parameters.Add(DBObjects.Person.cellphone1.ToString(), OleDbType.VarChar).Value = person.cellphone1;
                command.Parameters.Add(DBObjects.Person.cellphone2.ToString(), OleDbType.VarChar).Value = person.celphone2;
                command.Parameters.Add(DBObjects.Person.telephone.ToString(), OleDbType.VarChar).Value = person.telephone;
                command.Parameters.Add(DBObjects.Person.obra.ToString(), OleDbType.VarChar).Value = person.obra;
                command.Parameters.Add(DBObjects.Person.email.ToString(), OleDbType.VarChar).Value = person.email;
                command.Parameters.Add(DBObjects.Person.extraInformation.ToString(), OleDbType.VarChar).Value = person.extraInformaation;
                //command.Parameters.Add(DBObjects.Person.userId.ToString(), OleDbType.VarChar).Value = person.userId;

                DataConnection.Open(conn);

                command.ExecuteNonQuery();
            }
        }

        public Person Get(int id)
        {
            Person res = new Person();

            using (OleDbConnection conn = new OleDbConnection(connection))
            {
                DataSet ds = new DataSet("Person");

                string statement =
                    string.Format("SELECT * FROM Person WHERE {0} = {1}"
                    , DBObjects.Person.userId.ToString()
                    , id);

                OleDbDataAdapter adapter = new OleDbDataAdapter(statement, conn);

                DataConnection.Open(conn);

                adapter.Fill(ds);

                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    res = MapPerson(dr);
                }
            }
            return res;
        }

        public List<Person> Listby(DBObjects.Person field, string q)
        {
            List<Person> res = new List<Person>();

            using (OleDbConnection conn = new OleDbConnection(connection))
            {
                DataSet ds = new DataSet("Person");

                string statement =
                    string.Format("SELECT * FROM Person WHERE {0} = {1} ORDER BY {0}"
                    , field.ToString()
                    , q);

                OleDbDataAdapter adapter = new OleDbDataAdapter(statement, conn);

                DataConnection.Open(conn);

                adapter.Fill(ds);

                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    res.Add(MapPerson(dr));
                }
            }
            return res;
        }

        public void Update(Person person)
        {
            using (OleDbConnection conn = new OleDbConnection(connection))
            {
                string statement =
                    string.Format("UPDATE Person SET {0} = ?, {1} = ?, {2} = ?, {3} = ?, {4} = ?, {5} = ?, {6} = ?, {7} = ?, WHERE {8} = ?;"
                    , DBObjects.Person.name.ToString()
                    , DBObjects.Person.surname.ToString()
                    , DBObjects.Person.cellphone1.ToString()
                    , DBObjects.Person.cellphone2.ToString()
                    , DBObjects.Person.telephone.ToString()
                    , DBObjects.Person.obra.ToString()
                    , DBObjects.Person.email.ToString()
                    , DBObjects.Person.extraInformation.ToString()
                    , DBObjects.Person.userId.ToString());

                OleDbCommand command = new OleDbCommand(statement, conn);

                command.Parameters.Add(DBObjects.Person.name.ToString(), OleDbType.VarChar).Value = person.Name;
                command.Parameters.Add(DBObjects.Person.surname.ToString(), OleDbType.VarChar).Value = person.Surname;
                command.Parameters.Add(DBObjects.Person.cellphone1.ToString(), OleDbType.VarChar).Value = person.cellphone1;
                command.Parameters.Add(DBObjects.Person.cellphone2.ToString(), OleDbType.VarChar).Value = person.celphone2;
                command.Parameters.Add(DBObjects.Person.telephone.ToString(), OleDbType.VarChar).Value = person.telephone;
                command.Parameters.Add(DBObjects.Person.obra.ToString(), OleDbType.VarChar).Value = person.obra;
                command.Parameters.Add(DBObjects.Person.email.ToString(), OleDbType.VarChar).Value = person.email;
                command.Parameters.Add(DBObjects.Person.extraInformation.ToString(), OleDbType.VarChar).Value = person.extraInformaation;
                command.Parameters.Add(DBObjects.Person.userId.ToString(), OleDbType.VarChar).Value = person.userId;

                DataConnection.Open(conn);

                command.ExecuteNonQuery();
            }
        }

        //public List<Person> List(Person person)
        //{

        private Person MapPerson(DataRow dr)
        {
            Person person = new Person();

            person.Name = dr[DBObjects.Person.name.ToString()].ToString();
            person.Surname = dr[DBObjects.Person.surname.ToString()].ToString();
            person.cellphone1 = dr[DBObjects.Person.cellphone1.ToString()].ToString();
            person.celphone2 = dr[DBObjects.Person.cellphone2.ToString()].ToString();
            person.obra = dr[DBObjects.Person.obra.ToString()].ToString();
            person.email = dr[DBObjects.Person.email.ToString()].ToString();
            person.extraInformaation = dr[DBObjects.Person.extraInformation.ToString()].ToString();
            person.userId = Convert.ToInt32(dr[DBObjects.Person.userId.ToString()].ToString());

            return person;

        }

    }
}
 